package com.emp.database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.bean.Dept;
import com.bean.Emp;

import DataBase.DataBase;

public class EmpOperate extends DataBase{
	//给员工表添加员工信息
	Statement sta = null;
	
	//定义存储结果集的对象
	ResultSet rs = null;
	
	public List<Emp> getOneEmp(int eno){
		
		getcon();
		String sql="select * from emp where empno="+eno;
		List<Emp> list=new ArrayList<Emp>();
		try {
			sta=con.createStatement();
			rs=sta.executeQuery(sql);
			Emp emp=null;
			while(rs.next()){
				emp=new Emp();
				emp.setEmpno(rs.getInt("empno"));
				emp.setEname(rs.getString("ename"));
				emp.setJob(rs.getString("job"));
				emp.setHiredate(rs.getDate("hiredate"));
				list.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			try {
				rs.close();
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public List<Dept> getOneDept(int dno){		
		getcon();
		String sql="select * from dept where deptno="+dno;
		List<Dept> list=new ArrayList<Dept>();
		try {
			sta=con.createStatement();
			rs=sta.executeQuery(sql);
			Dept dept=null;
			while(rs.next()){
				dept=new Dept();
				dept.setDeptno(rs.getInt("deptno"));
				dept.setDname(rs.getString("dname"));
				dept.setLoc(rs.getString("loc"));
				list.add(dept);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			try {
				rs.close();
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	
	
	public List<Emp> getAllEmp(int currentPage,int pageRow)  {
		getcon();
		String sql="select * from (select rownum as num,t.* from emp t) where num>("+currentPage+"-1)*"+pageRow+" and num<="+currentPage+"*"+pageRow+"";
		List<Emp> list = null;
		try {
			sta = con.createStatement();
			rs = sta.executeQuery(sql);
			Emp emp=null;
			list = new ArrayList<Emp>();
			while(rs.next()){
				emp=new Emp();
				emp.setEmpno(rs.getInt("empno"));
				emp.setEname(rs.getString("ename"));
				emp.setJob(rs.getString("job"));
				emp.setHiredate(rs.getDate("hiredate"));
				list.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
		try {
			rs.close();
			sta.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		}
		return list;
	}
	
	public List<Dept> getAllDept(int currentPage,int pageRow)  {
		getcon();
		String sql = "select * from (select rownum as num,t.* from dept t) where num>("+currentPage+"-1)*"+pageRow+" and num<=("+currentPage+")*"+pageRow+" ";
		List<Dept> list =null;
		Dept dept=null;
		list=new ArrayList<Dept>();
		try {
			sta=con.createStatement();
			rs=sta.executeQuery(sql);
			while(rs.next()){
				dept=new Dept();
				dept.setDeptno(rs.getInt("deptno"));
				dept.setDname(rs.getString("dname"));
				dept.setLoc(rs.getString("loc"));
				list.add(dept);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	//计算尾页
	public int getLastPage(int pageRow){
		int totalnum=0;
		int lastPage=0;
		getcon();
		String sql="select count(*) from emp";
		try {
			sta=con.createStatement();
			rs=sta.executeQuery(sql);
			while(rs.next()){
				totalnum=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			try {
				rs.close();
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(totalnum%pageRow==0){
			lastPage=totalnum/pageRow;
		}else{
			lastPage=totalnum/pageRow+1;
		}
		return lastPage;
	}
	
	public int getLastPage2(int pageRow){
		getcon();
		String sql="select count(*) from dept";
		
		int lastPage =0;
		int totalNum =0;
		try {
		sta=con.createStatement();
		rs=sta.executeQuery(sql);
			while(rs.next()){
				totalNum=rs.getInt(1);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(totalNum%pageRow == 0){
			lastPage = totalNum/pageRow;
		}else{
			lastPage=totalNum/pageRow+1;
		}
		return lastPage;
		
	}
	
	
	
	
	
	public void addEmp(int empno,String ename,String job,String hiredate) {
		getcon();
		//编写sql
		String sql = "insert into emp (empno,ename,job,hiredate) values("+empno+",'"+ename+"','"+job+"',to_date('"+hiredate+"','yyyy-mm-dd'))";
		try {
			sta = con.createStatement();
			//发送sql语句在数据库中执行
			int flag = sta.executeUpdate(sql);
			System.out.println("影响的行数:"+flag);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	public void addDept(int deptno,String dname,String loc) {
		getcon();
		//编写sql
		String sql = "insert into dept (deptno,dname,loc) values("+deptno+",'"+dname+"','"+loc+"')";
		try {
			sta = con.createStatement();
			//发送sql语句在数据库中执行
			int flag = sta.executeUpdate(sql);
			System.out.println("影响的行数:"+flag);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	//根据员工编号修改员工姓名
	public void delEmp(int empno){
		getcon();
		String sql="delete  from emp where empno = "+empno+" ";
		try {
			sta = con.createStatement();
			//发送sql语句在数据库中执行
			int flag = sta.executeUpdate(sql);
			System.out.println("影响的行数:"+flag);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
	}
	
	public void delDept(int deptno){
		getcon();
		String sql="delete  from dept where deptno = "+deptno+" ";
		try {
			sta = con.createStatement();
			//发送sql语句在数据库中执行
			int flag = sta.executeUpdate(sql);
			System.out.println("影响的行数:"+flag);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
	}
	
	public void updateEmp(int empno,String ename,String job,String hiredate){
		getcon();
		String sql="update emp set ename='"+ename+"',job='"+job+"',hiredate=to_date('"+hiredate+"','yyyy-MM-dd') where empno="+empno+"";
		try {
			sta = con.createStatement();
			//发送sql语句在数据库中执行
			int flag = sta.executeUpdate(sql);
			System.out.println("影响的行数:"+flag);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
	}
	
	public void updateDept(int deptno,String dname,String loc){
		getcon();
		String sql="update dept set dname='"+dname+"',loc='"+loc+"' where deptno="+deptno+"";
		try {
			sta = con.createStatement();
			//发送sql语句在数据库中执行
			int flag = sta.executeUpdate(sql);
			System.out.println("影响的行数:"+flag);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
	}
	
	

}
